local luasql=require'luasql.sqlite3'
require'zdc.ds.dbutil'
require'zdc.ds.dbsub'
module(..., package.seeall)

local schemas = {}
schemas.sqlex_limit="LIMIT %s"

schemas.node = {[[
CREATE TABLE IF NOT EXISTS $tablename ( 
	id TEXT,
	pid TEXT,
	ik INTEGER,
	ts INTEGER,
	data BLOB,
	PRIMARY KEY(id)
);
]],
[[
CREATE INDEX IF NOT EXISTS $tablename{}_idx_pid on $tablename(pid);
]],
}

schemas.label = [[
CREATE TABLE IF NOT EXISTS $tablename{}_label ( 
	id TEXT,
	idx INTEGER,
	sum TEXT,
	timestamp TEXT,
	usr TEXT,
	PRIMARY KEY(id)
);]]

local _sqlite3 = {}
local _sqlite3_mt = {__metatable = {}, __index = _sqlite3}

zdc.ds.dbutil.MakeInhMT(_sqlite3)

function new(params)
	local env = luasql.sqlite3()
	local obj = {env=env,con=con,params=params}
	setmetatable(obj, _sqlite3_mt)
	obj:onrestore()
	return obj._sub[obj.params.tablenode or "node"],obj
end

function _sqlite3:subdb(tablenode)
	return zdc.ds.dbsub.new(self,schemas,tablenode)
end

function _sqlite3:init()
	assert(self:execute('PRAGMA synchronous = OFF;'))
	assert(self:execute('PRAGMA cache_size = 2000;'))
	assert(self:execute('PRAGMA temp_store = MEMORY;'))
	self:subdb(self.params.tablenode or "node")
end

function _sqlite3:escape(value)
	if value:find("%z") then
		error("db.sqlite3 cannot store embedded zeros")
	end
	return self.con:escape(value)
end

function _sqlite3:fmt_type(t)
	return t or 'TEXT'
end
